/***************************************************************
*  Copyright (c) 2007 by GroupMe! Team (www.groupme.net)
*  All rights reserved.
*
*  This file is part of the GroupMe! Project. Source code of 
*  this project is closed and redistribution of this code is
*  prohibited. 
*  
*  Contact: http://www.groupme.net
*
*  This copyright notice MUST APPEAR in all copies of the file!
***************************************************************/
package net.groupme.utils.sql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

import javax.sql.DataSource;

import net.groupme.model.ObjectFactory;


/**
 * created on Jul 23, 2007
 * 
 * This class provides some select query utility functions.
 * 
 * @author Fabian Abel, <a href="mailto:abel@l3s.de">abel@l3s.de</a>
 * @author last edited by: $Author: fabian $
 * 
 * @version $Revision: 1.3 $ $Date: 2008-05-28 16:13:05 $
 */
public class SelectQueryUtility {
	
	/** indicator that no result set was found */
	public static final int NO_RESULT_SET_FOUND = -1;
	
	/**
	 * Performs a simple query of the form:  <code>SELECT column FROM table WHERE id = ..</code>
	 * 
	 * @param ds the data source
	 * @param colum column name of the column whose value should be returned as a string
	 * @param table the table
	 * @param id the id of the table row
	 * @return the value as a string at table.column WHERE id = ... or <code>null</code> if no value is stored
	 */
	public static final String getSingleStringFromTableWhereId(DataSource ds, String column, String table, String id) {
		GetStringFromTableWhereIDQuery stringQuery = new GetStringFromTableWhereIDQuery(ds, column, table, id);
		List result = stringQuery.execute();
		Iterator iter = result.iterator();
		if (iter.hasNext()){
			return iter.next().toString();
		}else{
			return null;
		}
	}
	
	/**
	 * Performs a simple query of the form:  <code>SELECT column FROM table WHERE id = ..</code>
	 * 
	 * @param ds the data source
	 * @param colum column name of the column whose value should be returned as a string
	 * @param table the table
	 * @param id the id of the table row
	 * @return the value as an integer at table.column WHERE id = ... or <code>-1</code> if no value is stored
	 */
	public static final int getSingleIntFromTableWhereId(DataSource ds, String column, String table, String id) {
		GetIntFromTableWhereIDQuery stringQuery = new GetIntFromTableWhereIDQuery(ds, column, table, id);
		List result = stringQuery.execute();
		Iterator iter = result.iterator();
		if (iter.hasNext()){
			return (Integer) iter.next();
		}else{
			return NO_RESULT_SET_FOUND;
		}
	}
	
	/**
	 * Performs a simple query that should return a single integer
	 * 
	 * @param ds the data source
	 * @param query the query to perform
	 * @param colum column name of the column whose value should be returned as an integer
	 * @return the value as an integer or -1 if no result was found
	 */
	public static final int getSingleIntFromTable(DataSource ds, String query, String column) {
		GetIntQuery intQuery = new GetIntQuery(ds, query, column);
		List result = intQuery.execute();
		if(result == null){
			return NO_RESULT_SET_FOUND;
		}
		Collections.reverse(result);
		Iterator iter = result.iterator();
		if (iter.hasNext()){
			return (Integer) iter.next();
		}else{
			return NO_RESULT_SET_FOUND;
		}
	}

	
	/**
	 * Executes the given query on the GroupMe! database using {@link ObjectFactory#ds} as datasource.
	 * @param query query to execute
	 * @return results of the query or <code>null</code> if an error occured
	 */
	public static final ResultSet executeQuery(String query){
		Statement sqlStatement = null;
		try {
			sqlStatement = ObjectFactory.ds.getConnection().createStatement();
			ResultSet res =  sqlStatement.executeQuery(query);
			ObjectFactory.ds.getConnection().close();
			return res;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

}
